﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlServerCe;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using IntegrationExample;

namespace Delta.Forms
{
    public partial class ForDataBases : Form
    {
        private string ConnectionString;

        public string ExcelFileName;

        public List<int> UnemptyTables;

        public ForDataBases(string connectionString)
        {
            InitializeComponent();
            ConnectionString = connectionString;

            SqlCeConnection sqlCeConnection = new SqlCeConnection(ConnectionString);
            sqlCeConnection.Open();
            SqlCeCommand sqlCeCommand = new SqlCeCommand("SELECT [2] FROM __SysObjects WHERE [5]=-1",
                sqlCeConnection);
            SqlCeDataReader sqlCeDataReader = sqlCeCommand.ExecuteReader();
            List<string> nameTables = new List<string>();
            while (sqlCeDataReader.Read())
            {
                string str = sqlCeDataReader.GetString(0);
                lbTables.Items.Add(str);
            }
            sqlCeConnection.Close();
        }

        /// <summary>
        /// Получение всех таблиц из БД
        /// </summary>
        private void FromDB(Action<List<string[]>, string> actionString)
        {
            SqlCeConnection sqlCeConnection = new SqlCeConnection(ConnectionString);
            sqlCeConnection.Open();
            SqlCeCommand sqlCeCommand =
                new SqlCeCommand("SELECT [2] FROM __SysObjects WHERE [5]=-1",
                sqlCeConnection);
            SqlCeDataReader sqlCeDataReader = sqlCeCommand.ExecuteReader();
            List<string> nameTables = new List<string>();
            while (sqlCeDataReader.Read())
            {
                string str = sqlCeDataReader.GetString(0);
                nameTables.Add(str);
            }
            nameTables.ForEach(x =>
            {
                List<string[]> data = new List<string[]>();
                sqlCeCommand =
                    new SqlCeCommand("SELECT [2] FROM __SysObjects WHERE ([1] = '" + x + "')",
                    sqlCeConnection);
                sqlCeDataReader = sqlCeCommand.ExecuteReader();
                List<object> li = new List<object>();
                while (sqlCeDataReader.Read())
                {
                    int n = sqlCeDataReader.FieldCount;
                    object[] objects = new object[1];
                    sqlCeDataReader.GetValues(objects);
                    li.Add(objects[0]);
                }
                data.Add(li.Select(y => y.ToString()).ToArray());

                sqlCeCommand = new SqlCeCommand("SELECT * FROM [" + x + "]", sqlCeConnection);
                sqlCeDataReader = sqlCeCommand.ExecuteReader();
                while (sqlCeDataReader.Read())
                {
                    int n = sqlCeDataReader.FieldCount;
                    object[] objects = new object[n];
                    sqlCeDataReader.GetValues(objects);
                    data.Add(objects.Select(y => y.ToString()).ToArray());
                }
                actionString(data, x);
            });
            sqlCeConnection.Close();
        }

        /// <summary>
        /// Передача таблиц из БД в Excel
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnSendInExcel_Click(object sender, EventArgs e)
        {
            clbTables.Items.Clear();
            btnSendAllTables.Enabled = false;
            btnSendChosenTables.Enabled = false;

            WithExcel ww = new WithExcel();
            FromDB((x, y) => ww.InExcel(x, y));
            ww.CloseExcel();
        }

        /// <summary>
        /// Передача всех таблиц из Excel
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnSendAllTablesInDB_Click(object sender, EventArgs e)
        {
            for (int i = 0; i < clbTables.Items.Count; i++)
            {
                clbTables.SetItemChecked(i, true);
            }
            CreateTablesInDB();
        }

        /// <summary>
        /// Передача выбранных таблиц из Excel
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnSendChosenTables_Click(object sender, EventArgs e)
        {
            CreateTablesInDB();
        }

        /// <summary>
        /// Передача списка непустых таблиц из Excel
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnGetTablesListFromExcel_Click(object sender, EventArgs e)
        {
            clbTables.Items.Clear();
            btnSendAllTables.Enabled = false;
            btnSendChosenTables.Enabled = false;

            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Filter = "Microsoft Excel Workbooks (*.xls,*xlsx)|*.xlsx;*.xls|All Files|*.*";
            if (ofd.ShowDialog() == DialogResult.OK)
            {
                ExcelFileName = ofd.FileName;

                try
                {
                    WithExcel wex = new WithExcel(ExcelFileName);

                    int count = wex.GetLists();
                    string nameTable = String.Empty;
                    UnemptyTables = new List<int>();
                    for (int i = 0; i < count; i++)
                    {
                        int countSheets = wex.GetUnemptyTables(ref nameTable);
                        if (countSheets > 0)
                        {
                            clbTables.Items.Add(nameTable);
                            UnemptyTables.Add(i + 1);
                        }
                    }
                    wex.CloseExcel();
                    btnSendAllTables.Enabled = true;
                    btnSendChosenTables.Enabled = true;
                }
                catch(Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                    MessageBox.Show("Невозможно открыть файл", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
        }

        /// <summary>
        /// Передача выбранных таблиц в БД
        /// </summary>
        private void CreateTablesInDB()
        {
            if (clbTables.CheckedIndices.Count > 0)
            {
                WithExcel wex = new WithExcel(ExcelFileName);

                int count = wex.GetLists();

                string nameTable = String.Empty;
                for (int i = 0; i < clbTables.CheckedIndices.Count; i++)
                {
                    List<string[]> str = wex.FromExcel(ref nameTable,
                        UnemptyTables[clbTables.CheckedIndices[i]]);
                    SqlCeConnection sqlCeConnection = new SqlCeConnection(ConnectionString);
                    sqlCeConnection.Open();

                    string strCreateColumns = "(";
                    str[0].ToList().ForEach(x => strCreateColumns += "[" + x + "] nvarchar (50),");
                    strCreateColumns = strCreateColumns.TrimEnd(new char[] { ',' });
                    strCreateColumns += ")";
                    SqlCeCommand sqlCeCommand = new SqlCeCommand(
                        "CREATE TABLE [" + nameTable + "] " + strCreateColumns, sqlCeConnection);
                    try
                    {
                        sqlCeCommand.ExecuteNonQuery();
                    }
                    catch (SqlCeException exCreate)
                    {
                        MessageBox.Show(exCreate.ToString(), "Ошибка при создании таблицы БД", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    }

                    for (int j = 1; j < str.Count; j++)
                    {
                        string strInsertIntoTable = "(";
                        str[j].ToList().ForEach(x => strInsertIntoTable += " '" + x + "',"); 
                        strInsertIntoTable = strInsertIntoTable.TrimEnd(new char[] { ',' });
                        strInsertIntoTable += ")";
                        try
                        {
                            sqlCeCommand =
                                new SqlCeCommand("INSERT INTO [" + nameTable + "] VALUES " + strInsertIntoTable,
                                    sqlCeConnection);
                            sqlCeCommand.ExecuteNonQuery();
                        }
                        catch (SqlCeException exInsert)
                        {
                            MessageBox.Show(exInsert.ToString(), "Ошибка при заполнении таблицы БД", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        }
                    }
                    lbTables.Items.Clear();
                    sqlCeCommand = new SqlCeCommand("SELECT [2] FROM __SysObjects WHERE [5]=-1",
                        sqlCeConnection);
                    SqlCeDataReader sqlCeDataReader = sqlCeCommand.ExecuteReader();
                    List<string> nameTables = new List<string>();
                    while (sqlCeDataReader.Read())
                    {
                        string strTableName = sqlCeDataReader.GetString(0);
                        lbTables.Items.Add(strTableName);
                    }
                    sqlCeConnection.Close();
                }
                wex.CloseExcel();
                for (int i = 0; i < clbTables.Items.Count; i++)
                {
                    clbTables.SetItemChecked(i, false);
                }
            }
            else
            {
                MessageBox.Show("Таблицы не выбраны", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        /// <summary>
        /// Вызов формы с содержимым выбранной таблицы БД 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void lbTables_DoubleClick(object sender, EventArgs e)
        {
            if (lbTables.SelectedIndex > -1)
            {
                ViewDBTable vdbf = new ViewDBTable(ConnectionString, 
                    lbTables.SelectedItem.ToString());
                vdbf.ShowDialog();
                lbTables.SelectedIndex = -1;
            }
        }
    }
}